Optimized collection of just-in-time statistics for database query optimization

ABSTRACT

A system and method are provided for updating database statistics for use in generating query execution plans. A first query for a database is received, for example by a compiler, and the compiler accesses statistics associated with the first query for optimizing a query execution plan for the first query. The statistics may be accessed from a first cache. If the statistics retrieved from the first cache are determined to be stale, the system obtains updated statistics and may store them in a temporary cache. The stale statistics in the first cache are marked as invalid. Existing queries using the first cache may continue to use the first cache, while new queries may be provided with updated statistics.

TECHNICAL FIELD

This invention relates generally to database management systemsimplemented in computer systems, and in particular to statisticscollection for optimizing queries.

DESCRIPTION OF THE RELATED ART

Database query optimizers in database systems rely on statistics (forexample, the number of rows in a table, the number of distinct values ina column, the most frequently-occurring values in a column, thedistribution of data values in a column, and the like) that characterizethe data in order to choose appropriate query execution plans toretrieve data needed to answer queries. These statistics may bedetermined from an inspection of the tables of the database, or from aset of stored statistical values.

When statistical values are stored, the actual statistics of the currentdatabase may differ from the stored statistics, because the storedstatistical values may not be updated during data manipulations such asinsert, update, delete, and load, because updating the storedstatistical values during such data manipulations may not be anefficient use of the database system resources. However, the use ofinaccurate or outdated database statistics may result in less optimalquery execution plans and longer query processing times.

SUMMARY OF THE INVENTION

In accordance with a first aspect of the embodiments described herein,there is provided a method for updating database statistics for use ingenerating query execution plans, comprising receiving a first query fora database; determining whether valid and updated statistics forcompiling the first query are available from a first store comprisingstatistics associated with the database; and if valid and updatedstatistics for compiling the first query are not available from thefirst store, obtaining updated statistics from a further storecomprising statistics associated with the database.

In accordance with a further aspect, there is provided a method forupdating database statistics for a database table distributed over aplurality of partitions, each of the plurality of partitions comprisinga statistic value associated with each partition of the database table,the method comprising maintaining a cached version of each statisticvalue associated with each partition of the database table on eachpartition where a query relating to the database table may be compiled;determining whether the cached version of a statistic value on a firstpartition is expired; if the cached version of the statistic value onthe first partition is expired, obtaining an updated statistic valuefrom the plurality of partitions; and storing the updated statisticvalue as the cached version of the statistic value on the firstpartition.

In still a further aspect, there is provided a system for updatingdatabase statistics for use in generating query execution plans, thesystem comprising a database; a first store comprising statisticsassociated with the database; a second store comprising statisticsassociated with the database; and a compiler in communication with thedatabase and with at least the first store, the compiler beingconfigured for receiving queries for the database, accessing statisticsfor compiling each query from at least the first store, and generatingat least one query execution plan for executing each query, wherein thecompiler is further configured to: determine whether valid and updatedstatistics for compiling a first query are available from the firststore; and if valid and updated statistics for compiling the first queryare not available from the first store, obtain updated statistics fromthe second store.

There is also provided a computer program product comprising acomputer-readable medium, the computer-readable medium being encodedwith a computer program for updating database statistics, wherein thecomputer program, when executed on a computer system, causes thecomputer system to receive a first query for a database; determinewhether valid and updated statistics for compiling the first query areavailable from a first store comprising statistics associated with thedatabase; if valid and updated statistics for compiling the first queryare not available from the first store, obtain updated statistics from afurther store comprising statistics associated with the database.

BRIEF DESCRIPTION OF THE DRAWINGS

In drawings which illustrate by way of example only a preferredembodiment of the invention,

FIG. 1 is a schematic representation of a database management systemcomprising a metadata store.

FIG. 2 is a schematic representation of a database management systemcomprising a metadata store, a metadata cache, and a temporary metadatacache.

FIG. 3 is a further schematic representation of aspects of the system ofFIG. 2.

FIG. 4 is a representation of data in the metadata cache of FIG. 3.

FIG. 5 is a flowchart representation of a method for updating statisticsand/or other metadata in the system of FIG. 2.

FIG. 6 is a schematic representation of a partitioned database systemwith corresponding metadata in the database management system of FIG. 2.

FIG. 7 is a flowchart representation of a method for updating statisticsand/or other metadata in the system of FIG. 6.

FIG. 8 is a block diagram of a computer system for implementing thesystem of FIG. 2 or the method of FIG. 7.

DETAILED DESCRIPTION

The embodiments described below relate to a relational databasemanagement system or systems (DBMS), and to statistics collection foroptimizing queries in a DBMS. Computer systems comprising DBMS softwareusing a Structured Query Language (SQL) interface are well known in theart. One example is the DB2® data server from International BusinessMachines Corp., Armonk, N.Y. The SQL interface has been adopted as astandard by both the American National Standards Institute (ANSI) andthe International Standards Organization (ISO), although some DBMS alsocomprise proprietary extensions. Such standards, and proprietaryextensions, are known to those of ordinary skill in the art.

A process for handling queries in a DBMS 10 is shown in FIG. 1. The DBMS10 comprises at least one database 110, which may be distributed acrossone or more partitions within the system 10. The database 110 maycomprise one or more tables (not shown in FIG. 1). The database 110 isin communication with a compiler 130, an execution engine 140, andoptionally with a metadata store 160 that may store statistics and othermetadata relating to the database 110.

As shown in FIG. 1, a query 120 is received at the compiler 130 foroptimization. The query 120 may have originated within the DBMS 10, orfrom a client, not shown, in communication with the DBMS 10. The clientmay comprise a client application such as an online analyticalprocessing application, and may be remotely located from the DBMS 10.Further, the query 120 may be a simple query requiring data from asingle table of the database 110, or it may be a complex query includingjoined tables, aggregate functions, or predicates, or a combination ofone or more of these items.

The compiler 130 compiles, using the query 120, a query execution planor access plan 135. The compiler 130 may comprise a query optimizer thatanalyzes statistics associated with the tables targeted by the query 120in order to select an appropriate query execution plan 135 from among anumber of possible query execution plans. An appropriate or good queryexecution plan 135 is typically one that involves the least estimatedcost, based on the statistics available to the compiler 130. Forexample, the query optimizer may use the statistics to estimate thecardinality (i.e., the number of rows processed) at each intermediatestep of each query execution plan in order to estimate the total cost ofeach plan, and select the query execution plan with the lowestassociated cost. Once the query execution plan 135 is compiled, it isprovided to the execution engine 140. The execution engine 140 thenexecutes the query 120, accessing the database 110 and the target tablestherein according to the query execution plan 135. The output 170 fromthe execution engine 140 is the result set produced by the executionengine. This output 170 may then be provided to the client that hadsubmitted the query 120, or to a different destination such as an outputinterface (e.g., a display screen) or a storage unit (e.g., a computermemory), not shown.

The query optimizer of the compiler 130 may obtain statistics relatingto the target tables of the database 110 directly from the database 110,in which case a process computes the needed statistics directly from thedatabase 110, or directly or indirectly from the metadata store 160.Obtaining statistics from the metadata store 160 is generally moreefficient than computing statistics directly from the database 110, Themetadata store 160 may comprise special system tables, or system catalogtables, within the database 110 itself, and may comprise metadatarelating to the database 110 beyond statistics for the database 110. Themetadata store 160 may further comprise statistics relating to othermetadata for the database 110. In another embodiment, the metadata store160 may be comprised in a separate store or memory location from thedatabase 110. Either the contents of the metadata store 160, or a subsetof statistics and optionally other metadata from the metadata store 160,may be loaded from the metadata store 160 into a metadata cache 162,which may be stored in a memory location accessible to all users andprocesses of the DBMS 10. The compiler 130 may therefore obtainstatistics 165 from the metadata cache 162, rather than directly fromthe metadata store 160. However, sometimes any statistics relevant tothe query 120 in the metadata cache 162 cached from the metadata store160 are not accurate or up-to-date, and the actual, real-time statisticsof the tables in the database 110 in the metadata cache 162 and metadatastore 160 may differ from the stored values due to updates that tookplace on the database table data since the statistics were lastcollected. Database statistics may not be incrementally updated in themetadata store 160 or cache 162 during data manipulations such asinsert, update, delete and load, if such incremental maintenance is tooexpensive in the operation of the DBMS 10. The presence of inaccurate oroutdated statistics may cause the query optimizer to inaccuratelyestimate the cardinalities and costs of the steps in a query executionplan, which can result in choosing a plan that is suboptimal and has along query execution time.

Further, the metadata cache 162 comprising the statistics, while it maybe accessed by the compiler 130 independently of the database 110, mayneed to be accessed a number of times concurrently by a plurality ofprocesses executing within the DBMS 10. Thus, the data in the metadatacache 162 is shared in read-only mode by all processes (e.g., compilerprocesses compiling query execution plans for different queries). If,however, the statistics in the metadata cache 162 require updating as aresult of a change to a corresponding table in the database 110, thenthe process (e.g., query compilation process) that collected the updatedstatistics and seeks to write the updated data to the statistics in themetadata cache 162 will require an exclusive lock on the relevantmetadata cache entry. This approach may lead to inefficiencies in theoperation of the DBMS 10: first, in order to obtain this exclusive lock,the process seeking to write to the metadata cache 162 must wait for allother processes to finish reading the entry; then, while it has anexclusive lock and is writing the updated statistics to the metadatacache 162, all other processes must wait for the lock to be released,resulting in slower query execution. Alternatively, rather than delaythe compilation and execution of other queries dependent on the samestatistics, the process holding the updated statistics may simply throwaway the updated statistics, which increases the general overhead of theDBMS 10.

To maintain the database statistics in an updated condition, anautomatic statistics collection daemon (not shown) may be implemented inthe DBMS 10. The automatic statistics collection daemon may be activatedperiodically to collect a set of predetermined statistics by queryingthe database 110 for relevant data and computing statistics from thatdata, which are then stored in the metadata store 160. In a large andactive system, however, it may be difficult to determine the priorityand order in which statistics should be collected without overwhelmingthe database system resources by collecting too many statistics.Further, if the daemon is woken up only on a periodic basis, it may missa critical window for collecting statistics (for example, shortly beforethe compiler 130 compiles a complex query where optimization relies onparticularly accurate table statistics).

Thus, in accordance with the embodiment shown in FIG. 2, a system 20,such as a DBMS, is provided to propagate updated statistics for use byother processes. In this further system, a database 210 is provided.Again, the database 210 may comprise one or more tables containing data(not shown). Statistics and/or other metadata (including metadatarelating to stored statistics) for one or more of the tables of thedatabase 210 may be comprised in a store such as a metadata store 260,which, like the metadata store 160 of FIG. 1, may comprise systemcatalog tables comprised within the database 210 itself. The metadatastore 260 may be comprised in a separate store or memory location fromthe database 210. Statistics, and optionally other metadata, may beloaded into another store, such as a metadata cache 262, from themetadata store 260. A query 220 may be received at the compiler 230 forcompiling; as with FIG. 1, the query 220 may have originated within theDBMS 20 or from a client in communication with the DBMS 20. The compiler230 compiles, using the query 220, a query execution plan or access plan235. A query optimizer component of the compiler 230 accesses statistics265 for compiling, which may include optimizing, the query from themetadata cache 262. However, as will be explained below, if validstatistics are not available in the metadata cache 262, then thesestatistics are either loaded from the metadata store 260 into themetadata cache 262, for retrieval by the compiler 230 from the metadatacache 262; or, if available in another store, for example a temporarymetadata cache 264, they are loaded from this other store, or temporarycache 264, into the metadata cache 262 for retrieval by the compiler230. The temporary metadata cache 264 comprises an intermediate store ofstatistical data or other metadata for use by not only the compilerprocess compiling the query 220, but also by other compiler processesthat may be compiling other queries. The temporary metadata cache 264 isdescribed in further detail below with reference to FIG. 3.

The system then proceeds in a similar manner as that described withrespect to FIG. 1; once the query optimizer has determined the mostappropriate query execution plan 235 based on available statistics 265,the query execution plan 235 is passed to the execution engine 240 forexecution. The resultant output 270 is then provided to the client orother designated destination.

FIG. 3 further illustrates the system of FIG. 2 where there are multipleconcurrent compiler processes that may attempt to access the same set ofstatistics. A database 210, metadata store 260, and a store such asmetadata cache 262, such as those described above, are provided. Thedatabase 210 comprises at least one table, and may comprise a pluralityof tables, for example, Table 1 and Table 2 (not shown). The metadatacache 262 may be resident in computer memory, and loaded from disk withdata from the metadata store 260. Initially, the metadata cache 262 maycomprise a first entry of statistics and/or other metadata for Table 1,Table 1 Metadata 262 a (the metadata cache 262 may comprise statisticsand/or other metadata for other tables in the database 210, not shown inFIG. 3). A first query, Query 1, is received by the compiler, initiatinga compilation and optimization process for Query 1, as represented byQuery 1 Process 232 in FIG. 3. In the example of FIG. 3, Query 1requires data from Table 1 in the database 210, and accordingly theoptimization process 232 may utilize statistics and optionally othermetadata relating to Table 1 to evaluate the most appropriate queryexecution plan for Query 1. The Query 1 Process 232 first attempts toobtain the required statistics and/or metadata from the metadata cache262, as shown by arrow 232 a. If the metadata cache 262 contains therequired statistics and/or metadata for the Query 1 Process 232—forexample, the statistics contained in Table 1 Metadata 262 a—and thesestatistics and/or other metadata are valid and sufficiently updated asexplained below, then the Query 1 Process 232 may proceed to compileQuery 1. If the metadata cache 262 does not contain the required, validstatistics and/or metadata for the Query 1 Process 232 but another storesuch as the temporary metadata cache 264 comprising these statisticsand/or metadata is available, then the process 232 may cause thisrequired data to be loaded from the temporary metadata cache 264 intothe metadata cache 262, so that the process 232 may then access the datafrom the metadata cache 262. In this example, if the metadata cache 262did not contain valid data in the Table 1 Metadata 262 a for the Query 1Process 232, then the data may be loaded from the Table 1 Metadata 264 acomprised within the temporary metadata cache 264; if invalid data forTable 1 was already present in the metadata cache 262, then the cache262 will now comprise multiple entries of statistics and/or othermetadata for Table 1, although only one entry will be valid (an exampleof this is described in conjunction with the compilation of Query 2,below). If neither the metadata cache 262 nor the temporary metadatacache 264 comprises the required, valid statistics and/or metadata, thenthe required data may be loaded from the metadata store 260 intometadata cache 262, and the Query 1 Process 232 may then obtain thesedata from the metadata cache 262.

During or after the compilation of Query 1, the statistics and/or othermetadata relating to Table 1 in the database 210, as stored in themetadata cache 262, may change as a result of changes to the database210, and consequently the statistics and/or other metadata stored in themetadata cache 262 may become stale or out of date. While thecompilation or optimization of some queries may not be significantlyaffected by changes to the underlying database 210, the compilation oroptimization of other queries may be sensitive to such changes. Thus,for example, a second query, Query 2, may be received by the compilerand a new process, Query Process 2 234, initiated to compile andoptimize a query execution plan for Query 2. Query 2, in this example,likewise requires data from Table 1 in the database 210. Further, athird query, Query 3, may be received by the compiler and a thirdprocess, Query Process 3 236, initiated to compile and optimize a queryexecution plan for Query 3. In the example of FIG. 3, Query 3 relates totwo tables in database 210, Table 1 and Table 2. Each of these queries234, 236 would therefore attempt to obtain any needed statistics fromthe metadata cache 262, and would normally proceed as described above.The temporary metadata cache 264 may also comprise metadata stored inassociation with other tables of the database 210, for example Table 2Metadata 264 b; this additional metadata may be the result of a previousupdate. If the statistics for these other tables obtained by a queryfrom the metadata cache 262 are determined to be valid and sufficientlyupdated for compiling that query, then data will not be loaded againfrom that particular set of metadata (e.g., Table 2 Metadata 264 b) intothe metadata cache 262.

However, one of these subsequent queries, for example Query Process 2234, may determine that the statistics and/or other metadata it requiresfor Table 1 that it obtained using the method described above (i.e.,Table 1 Metadata 262 a from the metadata cache 262), are stale or out ofdate. If this is the case, then the process 234 may determine whetherand how to obtain the updated statistics and/or other metadata itrequires. For example, if updated statistics are not required for theQuery Process 2 234, but the statistics retrieved are determined to bestale, the Query Process 2 234 may register a request with a statisticsdaemon 250 to collect new statistics, but will still proceed with thestale statistics for the purpose of query compilation. Alternatively,the Query Process 2 234 may determine that new statistics should beobtained prior to compilation by scanning the table data for Table 1 inthe database 210, and computing new statistics, as illustrated by thearrow 234 a. If the Query Process 2 234 obtains these new statistics,then any new data thus obtained is then stored by the Query Process 2234 in the temporary metadata cache 264 as shown by arrow 234 b,overwriting any previous corresponding statistics or other metadata thatwas stored in the temporary cache 264. The corresponding data stored inthe metadata cache 262 is then marked by the Query Process 2 234 asinvalid, and the Query Process 2 234 proceeds to complete compilationand/or optimization of Query 2 using the new data thus obtained, whichmay be stored by the Query Process 2 234 in privately accessible memory(i.e., memory not accessible by another compiler process). In a furtherembodiment, the Query Process 2 234, after storing the updated data inthe temporary metadata cache 264, may force a load of the newly-acquireddata from the temporary metadata cache 264 into the metadata cache 262.This newly-acquired data would then be stored as a separate entry in themetadata cache 262, illustrated in FIG. 3 as new Table 1 Metadata 262 b.The first entry in the metadata cache 262, Table 1 Metadata 262 a, wouldthen be marked as invalid, such that only the most recently storedentry, Table 1 Metadata 262 b, is valid. Thus, in this furtherembodiment, metadata cache 262 may comprise multiple statistics and/orother metadata entries for Table 1, although at least one of thesemultiple entries will be marked as invalid, and only one Table 1 entrywill be valid. Invalid entries in the metadata cache 262 may be deletedfrom the metadata cache 262 as part of a cache space management routineor as a result of a request to load fresh statistics and/or metadatainto the metadata cache 262, provided no other query process isaccessing any of those invalid entries. By allowing for the creation ofmultiple entries in the metadata cache 262, query processes with updatedmetadata entries for loading into the metadata cache 262 need not waitfor other processes accessing existing entries for the same metadata torelease any latches (e.g., a share latch) on the data.

A determination of whether the statistics or other metadata retrieved bya process need to be updated may be implemented by any means known tothose skilled in the art. As an example, which is not meant to belimiting, a simple aging rule may be applied, such that statistics inthe metadata cache 262 with a timestamp older than a predetermined limitare deemed to be out of date, and in need of updating. Alternatively, astatistic may be deemed to be in need of updating after it has beenaccessed a predetermined number of times. A further method fordetermining whether a cached statistic is stale and in need of updatingis described with reference to FIG. 6, below.

A possible schematic arrangement of Table 1 metadata in the metadatacache 262 of FIG. 4 illustrates that the metadata cache 262 comprises atimestamp 406 reflecting the time and/or date of collection orverification of the statistics and/or metadata in the metadata cache262, as well as a flag 408 indicating whether the statistics and/ormetadata is valid; this flag 408 may be altered by a query process, suchas the Query Process 2 234 in the example of FIG. 3. In this example, a‘0’ may indicate that the data is valid, and a ‘1’ or non-zero value mayindicate that the data is invalid; however, the selection of appropriateformats and values for the flag 408 may be made by a person of ordinaryskill in the art. In a further embodiment, the validity of statisticsand/or metadata may be recorded with more granularity; for example, eachstatistical and/or metadata value may be provided with an associatedflag indicating whether each value is valid or invalid. The timestamp406 may be substituted with an appropriate counter value, if thestatistic is to be updated after a certain number of accesses. For eachstatistic or other metadata cached in the metadata cache 262, there maybe an identifier 402 for identifying the type of statistic or metadatastored in the metadata cache 262 as well as a corresponding value 404.

Returning to FIG. 3, Query Process 2 234 may also cause the updatedstatistics stored in the temporary metadata cache 264 to be persisted tothe metadata store 260. While the Query Process 2 234 itself may persistthe updated statistics to the store 260, in a preferred embodiment theQuery Process 2 234 registers a request with another process, such asthe statistics daemon 250, for the process to persist the updatedstatistics to the metadata store 260.

Query Processes 1 and 3 (232 and 236, respectively), may proceed tocomplete optimization using the existing data in the metadata cache 262,which may have since been marked as invalid by Query Process 2 234.Query Process 3 236 will also use the existing data in the metadatacache 262 for Table 2, which in this example is not updated with newstatistical data. After Query Process 2 234 has marked the Table 1metadata in the metadata cache 262 as invalid, Query Process 4 238 maybe initiated by the compiler in response to a subsequently receivedQuery 4, and may require statistical data for Table 1 of database 210.As described above, the Query Process 4 238 first accesses the metadatacache 262. If it determines that it requires statistics and/or metadatain the metadata cache 262 that have since been marked as invalid, thenQuery Process 4 238 may cause the required statistics to be loaded fromthe temporary metadata cache 264 into the metadata cache 262, asindicated by arrow 238 a, so that the Query Process 4 238 may thus loadthe required statistics from the metadata cache 262, as indicated byarrow 238 b. Thus, Query Process 4 238 will compile Query 4 using morerecent statistics than those stored in the metadata cache 262, andwithout having to obtain newer statistics itself from the database 210.

This process is further illustrated in FIG. 5. At block 500, a query isreceived for optimization by the compiler 230, and a query process isinitiated for compiling and/or optimizing the query. At block 505, adetermination is made whether valid statistics and/or other metadata areavailable in the metadata cache 262 for compiling and/or optimizing thequery; if there are, and if these statistics and/or other metadata aredetermined to be sufficiently up to date for the purpose of compilingthe query at block 515, then the query process obtains the statisticsand/or other metadata and compiles the query at block 540. If, however,valid statistics and/or other metadata are not available at the metadatacache 262, then the query process determines whether the required datais available from a temporary cache 264 at block 510. If the requireddata is available from the temporary cache 264, then the data is loadedfrom the temporary cache 264 into the metadata cache 262 at block 511.If the required data is not available from the temporary cache 264—forexample, if the temporary cache 264 does not comprise statistics and/orother metadata for the table or tables relevant to the query then therequired data may be loaded from the metadata store 260 into themetadata cache 262 at block 512.

The query process then determines whether this data (whether retrievedindirectly from the metadata store 260 or the temporary metadata cache264) is sufficiently updated at block 515; if so, then the query processcompiles the query at block 540.

If, however, it is determined at 515 that the data retrieved is notsufficiently up to date for use by the query process, then the queryprocess makes a determination at block 520 whether to compute newstatistics. If the query process determines that new statistics and/orother metadata are not required for the purpose of compiling the presentquery, the process proceeds to block 540; however, the process may alsoschedule the statistics daemon 250 to collect new statistics, which willbe used to update the metadata store 260. If the query processdetermines that new statistics and/or other metadata are required, thenit may proceed to obtain these new statistics and/or other metadata byscanning the database 210, and computing data as required at block 525.The query process registers a request with the temporary metadata cache264 indicating that it will be collecting statistics (and/or othermetadata) for the relevant table or tables for the query. Other queryprocesses that subsequently access the temporary cache 264 will thus beable to determine whether the statistics and/or other metadata theyrequire are in the process of being updated. In one embodiment, when afirst query process registers a request indicating that it will collectstatistics and/or other metadata, another query process may have in themeantime stored new statistics in the temporary metadata cache 264 sincethe first query process had determined that new statistics and/ormetadata were required. If the temporary metadata cache 264 comprisesmore updated statistics and/or metadata, which may be determined bycomparing the associated timestamp 406 in the temporary metadata cache264 with the time of the first query process's request, then the requestfrom the first query process may be rejected, and the new statisticsand/or metadata returned to the first query process instead. Othermethods of handling concurrent or near-concurrent query processesoperating on the same metadata and/or statistics may be implemented, andwill be understood by those skilled in the art.

The query process then stores these newly-collected data in thetemporary cache 264 at block 530, and indicates to the temporarymetadata cache 264 that collection has been completed. The existingcorresponding data in the metadata cache 262 is then marked as invalidat block 535. The query process may then proceed to compile and/oroptimize the query at block 540.

It will be appreciated by those skilled in the art that the foregoingsystem provides a mechanism for prioritizing and asynchronouslytriggering statistics collection for use by subsequent queries. Updatedstatistics and/or other metadata are stored in temporary metadata cache264 as required, as query processes determine that the cached statisticsin the metadata cache 262 and/or the metadata store 260 are stale.Further, as new statistics and/or other metadata are collected by thevarious processes, they are also persisted to the metadata store 260.The foregoing system for updating statistics in the DBMS 20 also reducesthe likelihood of redundant statistics computation and collection byconcurrently-running query processes, because a subsequently-initiatedprocess may determine from the temporary metadata cache 264 whetheranother query has already commenced new statistics collection from thedatabase 210. The subsequently-initiated query may then obtain thenewly-collected statistics indirectly from the metadata store 260, oncethe newly-collected statistics are persisted to the metadata store 260,or indirectly from the temporary metadata cache 264.

The foregoing embodiment may also be employed in a DBMS 20 forpropagating updated statistics that may be accessed across multiplepartitions in the database 210 so that the entire system may benefitfrom the updated statistics. It will be appreciated by those skilled inthe art that a database may be distributed across multiple partitions.In the prior art, statistics collection by various query processes maybe conducted independently in each partition, resulting in possibleduplication of statistics collection efforts, thus adding to theoverhead of the DBMS 20. However, in the implementation of theembodiment described above, statistical data may be cached andaccessible across all partitions of the database. Updates to thetemporary metadata cache are therefore not restricted to a singlepartition. As shown in FIG. 6, a database 300 is partitioned acrossthree partitions, 300 a, 300 b, and 300 c. In the schematic of FIG. 6,partition 300 a is a catalog partition, at which metadata (includingstatistics) is stored, for example metadata cache 262 a. The otherpartitions may store other metadata caches 262 b and 262 c. These cachesare similar to the metadata cache 262 of FIGS. 2 and 3. The catalogpartition 300 a also comprises the temporary metadata cache 264 and themetadata store 260. The statistics daemon 250 (not shown in FIG. 6) maypersist updated statistics and/or other metadata from the temporarymetadata cache 264 to the metadata store 260, and the data in thetemporary metadata cache 264 may be loaded into the metadata caches 262a, 262 b, and 262 c.

A method for determining whether statistics for a particular table in adatabase 300 require updating is also provided. The level of activity inrow modification in a given table may serve as an indicator of thefreshness of statistics previously collected for that table; thus, astatistic value such as a count of the number of rows that have beenupdated, deleted, and inserted in a table may be used to determinewhether previously collected statistics are now stale. For example, ifthe count exceeds a predetermined limit, the system may deem thestatistics associated with the table as being stale. Theupdated-deleted-inserted (“UDI”) row counts may be recorded separately,or tracked in a single counter value.

If the database 300 is distributed across multiple partitions, then thestatistic value or values—here, the UDI counter value or values—for agiven table may be maintained independently for each partition in whichthe table is stored. In that case, a query process, requiring UDI datato determine whether the cached statistics for a given table in thedatabase are sufficiently up to date, must retrieve and combine (forexample, by summing) the UDI data for that table from each of thosepartitions where the table resides. Retrieving UDI data from eachrelevant partition may slow compilation of a query execution plan,particularly when there are delays in network communication between thepartitions. If there is a large number of queries to be compiledreferencing that particular table, then significant overhead may beadded to the retrieval of the UDI data.

Accordingly, in the embodiment shown in FIG. 6, the UDI data for a giventable is cached at each database partition where a query may becompiled. UDI data 570 a is stored on the first partition 300 a. Acached copy of the UDI data 570 b is stored on partition 300 b, and acached copy of the UDI data 570 c is stored on partition 300 c. In theembodiment depicted in FIG. 6, the UDI data 570 a, 570 b, 570 ccomprises at least one counter value “counter_value”; here, the singlecounter value represents the updated, deleted, and inserted rows inTable 1, a timestamp, and a flag value. The flag value is used toindicate whether updated UDI data is being captured; for example, a “00”value may indicate that data is not being captured, whereas a value thatis not equal to “00” may indicate that data is being captured. The flag,of course, may take another format, provided it is capable ofidentifying at least these two states. By maintaining a cached copy ofthe UDI data (and of other metadata, such as Table 1 Metadata 500 b, 500c) on different partitions, a faster determination of the “staleness” orvalidity of the stored statistics or other metadata can be made.

The process of updating the UDI data may be understood with reference toFIG. 7. When a query process at a given partition determines that itrequires UDI data for a given table for compiling or optimizing a query,the process first accesses the cached UDI data at its partition. Atblock 600, the process checks the timestamp of the UDI data and thecorresponding flag value. If it is determined at block 605 that thetimestamp is not expired, or that the flag value is equal to TRUE (inthis embodiment, TRUE signifies that updated UDI data is being capturedby another process; in other embodiments, the TRUE value may correspondto the state in which UDI data is not currently being captured, oralternatively values other than TRUE and FALSE may be used to indicatewhether UDI data is in the process of being captured or not), then theprocess moves to block 655, and proceeds to compile the query with theUDI data read at the initial access.

Otherwise, the process proceeds to acquire a latch on the UDI cacheentry, obtain fresh UDI statistics, and update the cached UDI data. Thisupdating process may follow the flow of FIG. 7, beginning at block 610,where the process attempts to acquire a conditional latch on the UDIcache entry. If the latch is not successful at block 615, then theprocess moves to block 655, and proceeds to compile the query with theUDI data read at the initial access. If the latch is successfullyacquired, then the process may determine at block 620 whether thetimestamp is expired. If the timestamp is not expired, then the processmay then release the latch 660 and proceed to compile the query at 665.If the timestamp is expired, the process then determines at block 625whether the flag value is set to TRUE; if so, another process may haveupdated the UDI data prior to the present process's acquisition of thelatch at block 610. The present process may then release the latch 660and proceed to compile the query at 665, since updated UDI data isavailable.

If, however, the timestamp is determined to be expired at block 620 andthe flag value is FALSE, then the process sets the flag value to TRUE atblock 630 and releases the latch at block 635; thus, further processescompiling other queries that may access the cached UDI data on thispartition will see that the UDI data is in the process of being updated,and will continue compiling the other queries without waiting forupdated UDI data. This avoids delay in the compilation of other queries,thus reducing system overhead.

The process then moves to block 640, where it retrieves and sums UDIentries from each database partition 300 a, 300 b, 300 c on which thetable is resident to obtain updated UDI data. The process then acquiresa latch on the UDI cache entry at block 645, stores the updated UDI dataand updates the timestamp at block 650, and sets the flag value to FALSEat block 655 before releasing the latch at 660. The process thenproceeds to complete compilation of the query at block 665 with theupdated UDI data.

It will be appreciated by those skilled in the art that because thecached UDI data is stored with other table metadata on the databasepartition, the UDI data, timestamp, and flag may be reset whenever themetadata cache on that partition is reloaded. Further, the overhead onthe partition 300 a containing the UDI data is reduced, since calls onthat UDI data will be reduced; instead, query processes on otherpartitions may retrieve the cached UDI data 570 b, 570 c. The UDI datafor a given table need not be cached on each and every databasepartition on which the table resides; caches may be maintained only onselect partitions.

Thus, the foregoing embodiments provide a system, such as a DBMS, thatsuitably propagates and makes permanent on disk newly collectedstatistics for tables in a database, as those statistics are updated inresponse to the requirements of query processes. The foregoingembodiments also provide a mechanism to prioritize and asynchronouslytrigger statistics collection while reducing system overhead.

Although not illustrated in FIGS. 1 to 7, the DBMS 10 or 20 may includeadditional components, not shown, such as database administration ormaintenance modules. Further, the components of the DBMS 10 or 20 may beremotely located from one another, and one or more of the components mayalso include sub-components that are not shown. For example, thecompiler 130 or 230 may comprise a parser or other components.

The systems described herein may be implemented on a data processingsystem 700, such as that depicted in FIG. 8. The data processing system700 is suitable for storing and/or executing program code. The dataprocessing system 700 may include a processor 702, coupled to memoryelements 704 a through n via a system bus 706. The data processingsystem 700 may comprise a plurality of processors 702, coupled directlyor indirectly to memory elements through a system bus. The memoryelements 704 a through n may include volatile and non-volatile datastorage units, including read-only memory and random access memorycomponents, hard drives, optical storage media, and other readable andread-writable storage media, and may include local memory employedduring actual execution of the program code, bulk storage, and cachememories which provide temporary storage of at least some program codein order to reduce the number of times code must be retrieved from bulkstorage during execution. The metadata caches and subsets describedherein may be stored on one or more of the memory elements 704 a throughn as well. In addition, input/output devices 708 a through n, which mayinclude keyboards, displays, pointing devices, and the like, are alsocoupled to the data processing system 700, directly or indirectlythrough intervening input/output controllers (not shown). In a dataprocessing system, the components, software modules, functions and datastructures described herein may be connected directly or indirectly toeach other in order to allow the flow of data needed for theiroperations. It is also noted that a module or processor includes but isnot limited to a unit of code that performs a software operation, andcan be implemented for example as a subroutine unit of code, or as asoftware function unit of code, or as an object (as in anobject-oriented paradigm), or as an applet, or in a computer scriptlanguage, or as another type of computer code.

A network adaptor 710 may be provided, coupled to the data processingsystem 700 for enabling communication between the system 700 and otherdata processing systems, remote printers, client devices, or storagedevices, via communication link 712. Communication link 712 may be afixed or wireless connection, over either a private or public local orwide area network. Modems, cable modem and Ethernet cards are just a fewof the currently available types of network adapters.

The systems and methods disclosed herein are presented only by way ofexample and are not meant to limit the scope of the invention. Othervariations and modifications of the systems and methods described abovewill be apparent to those skilled in the art and as such are consideredto be within the scope of the invention, which includes all suchvariations and modifications as fall within the scope of the appendedclaims. For example, it should be understood that acts and the order ofthe acts in the processing described herein may be altered, modifiedand/or augmented, or that said acts may be carried out by softwareand/or hardware modules designed for such purpose, and still achieve thedesired outcome.

The invention can take the form of an entirely hardware embodiment, anentirely software embodiment or an embodiment containing both hardwareand software elements. In a preferred embodiment, the invention isimplemented in software, which includes but is not limited to firmware,resident software, microcode, etc. Furthermore, the invention can takethe form of a computer program product accessible from a computer-usableor computer-readable medium providing program code for use by or inconnection with a computer or any instruction execution system. For thepurposes of this description, a computer-usable or computer readablemedium can be any apparatus that can contain, store, communicate,propagate, or transport the program for use by or in connection with theinstruction execution system, apparatus, or device.

The systems' and methods' data may be stored in one or more media. Themedia can be comprised in many different types of storage devices andprogramming constructs, such as RAM, ROM, Flash memory, programming datastructures, programming variables, etc. It is noted that data structuresdescribe formats for use in organizing and storing data in databases,programs, memory, or other computer-readable media for use by a computerprogram. Media can be an electronic, magnetic, optical, electromagnetic,infrared, or semiconductor system (or apparatus or device). In a furtherembodiment, a propagation medium may be utilized. Examples of acomputer-readable medium that are not intended to be limiting include asemiconductor or solid state memory, magnetic tape, a removable computerdiskette, a random access memory (RAM), a read-only memory (ROM), arigid magnetic disk and an optical disk. Current examples of opticaldisks include compact disc-read only memory (CDROM), compactdisc-read/write (CD-R/W) and DVD.

Various embodiments of the present invention having been thus describedin detail by way of example, it will be apparent to those skilled in theart that variations and modifications may be made without departing fromthe invention. The invention includes all such variations andmodifications as fall within the scope of the appended claims.

1. A method for updating database statistics for use in generating queryexecution plans, comprising: receiving a first query for a table in adatabase; determining whether valid and updated statistics for compilingthe first query are available from a first store comprising a firstentry with statistics associated with the table in the database; ifvalid and updated statistics for compiling the first query are notavailable from the first store, obtaining updated statistics from afurther store comprising statistics associated with the table in thedatabase; and storing the obtained updated statistics associated withthe table in the database in the first store as a second entry, therebystoring multiple entries for the statistics associated with the sametable in the database, wherein the first store is a metadata cache andthe further store is a temporary cache.
 2. The method of claim 1,wherein obtaining updated statistics from the further store comprises:determining whether sufficiently updated statistics for compiling thefirst query are available from a second store comprising statisticsassociated with the table in the database; if sufficiently updatedstatistics for compiling the first query are not available from thesecond store, collecting the updated statistics from the database,storing the updated statistics in the second store, and retrieving theupdated statistics for compiling the first query from the second store.3. The method of claim 2, wherein retrieving the updated statistics forcompiling the first query from the second store comprises loading theupdated statistics from the second store into the first store, andretrieving the updated statistics from the first store.
 4. The method ofclaim 2, wherein the first store further comprises an indicator forindicating whether the statistics are valid.
 5. The method of claim 4,further comprising: upon storing the updated statistics in the secondstore, altering the indicator associated with corresponding statisticsin the first store to indicate that the corresponding statistics areinvalid.
 6. The method of claim 1, wherein obtaining updated statisticsfrom a further store comprises: determining whether sufficiently updatedstatistics for compiling the first query are available from a secondstore comprising statistics associated with the table in the database;if sufficiently updated statistics for compiling the first query are notavailable from the second store, retrieving the updated statistics froma third store comprising statistics associated with the table in thedatabase; storing the updated statistics in the first store as thesecond entry; and retrieving the updated statistics for compiling thefirst query from the second entry in the first store.
 7. The method ofclaim 1, wherein obtaining updated statistics from a further storecomprises: determining whether sufficiently updated statistics forcompiling the first query are available from a second store or a thirdstore comprising statistics associated with the table in the database;if sufficiently updated statistics for compiling the first query are notavailable from the second store or the third store, collecting theupdated statistics from the database, storing the updated statistics inthe second store, and retrieving the updated statistics for compilingthe first query from the second store.
 8. The method of claim 7, whereinretrieving the updated statistics for compiling the first query from thesecond store comprises loading the updated statistics from the secondstore into the first store, and retrieving the updated statistics fromthe first store.
 9. The method of claim 8, wherein the first storefurther comprises an indicator for indicating whether the statistics arevalid, the method further comprising: upon storing the updatedstatistics in. the second store, altering the indicator associated withcorresponding statistics in the first store to indicate that thecorresponding statistics are invalid.
 10. The method of claim 1,wherein, only one of the multiple entries is valid, while all other ofthe multiple entries are invalid.
 11. A system for updating databasestatistics for use in generating query execution plans, the systemcomprising: a database; a first store comprising statistics associatedwith a table in the database; a second store comprising a first entrywith statistics associated with the table in the database; and acompiler in communication with the database and with at least the firststore, the compiler being configured for receiving queries for the tablein the database, accessing statistics for compiling each query from atleast the first store, and generating at least one query execution planfor executing each query, wherein the compiler is further configured to:determine whether valid and updated statistics for compiling a firstquery are available from the first store; if valid and updatedstatistics for compiling the first query are not available from thefirst store, obtain updated statistics from the second store; and storethe obtained updated statistics associated with the table in thedatabase in the first store as a second entry, thereby storing multipleentries for the statistics associated with the same table in thedatabase, wherein the first store is a metadata cache and the secondstore is a temporary cache.
 12. The system of claim 11, wherein thecompiler is further configured to obtain updated statistics from thesecond store by causing the updated statistics to be loaded from thesecond store into the first store, and retrieving the updated statisticsfrom the first store.
 13. The system of claim 11, further comprising athird store comprising statistics associated with the database, andwherein the compiler is further configured to obtain updated statisticsfrom the second store by: determining whether sufficiently updatedstatistics for compiling the first query are available from the secondstore or the third store; if sufficiently updated statistics forcompiling the first query are not available from the second store or thethird store, collecting the updated statistics from the database,storing the updated statistics in the second store, and retrieving theupdated statistics for compiling the first query from the second store.14. The system of claim 13, wherein the compiler is further configuredto retrieve the updated statistics for compiling the first query fromthe second store by loading the updated statistics from the second storeinto the first store, and retrieving the updated statistics from thefirst store.
 15. The system of claim 11, wherein, only one of themultiple entries is valid, while all other of the multiple entries areinvalid.
 16. A computer program product comprising a computer-readablemedium, the computer-readable medium being encoded with a computerprogram for updating database statistics, wherein the computer program,when executed on a computer system, causes the computer system to:receive a first query for a table in the database; determine whethervalid and updated statistics for compiling the first query are availablefrom a first store comprising a first entry with statistics associatedwith the table in the database; if valid and updated statistics forcompiling the first query are not available from the first store, obtainupdated statistics from a further store comprising statistics associatedwith the table in the database; and store the obtained updatedstatistics associated with the table in the database in the first storeas a second entry, thereby storing multiple entries for the statisticsassociated with the same table in the database, wherein the first storeis a metadata cache and the further store is a temporary cache.
 17. Thecomputer program product of claim 16, wherein, only one of the multipleentries is valid, while all other of the multiple entries are invalid.18. The computer program product of claim 16, further causes thecomputer system to: determine whether sufficiently updated statisticsfor compiling the first query are available from a second storecomprising statistics associated with the table in the database; ifsufficiently updated statistics for compiling the first query are notavailable from the second store, collect the updated statistics from thedatabase, store the updated statistics in the second store, and retrievethe updated statistics for compiling the first query from the secondstore.
 19. The computer program product of claim 18, wherein the firststore further comprises an indicator for indicating whether thestatistics are valid.
 20. The computer program product of claim 19,further causes the computer system to: upon storing the updatedstatistics in the second store, alter the indicator associated withcorresponding statistics in the first store to indicate that thecorresponding statistics are invalid.
 21. The computer program productof claim 16, further causes the computer system to: determine whethersufficiently updated statistics for compiling the first query areavailable from a second store comprising statistics associated with thetable in the database; if sufficiently updated statistics for compilingthe first query are not available from the second store, retrieve theupdated statistics from a third store comprising statistics associatedwith the table in the database; store the updated statistics in thefirst store as the second entry; and retrieve the updated statistics forcompiling the first query from the second entry in the first store. 22.The computer program product of claim 16, further comprises causing thecomputer system to: determine whether sufficiently updated statisticsfor compiling the first query are available from a second store or athird store comprising statistics associated with the table in thedatabase; if sufficiently updated statistics for compiling the firstquery are not available from the second store or the third store,collect the updated statistics from the database, store the updatedstatistics in the second store, and retrieve the updated statistics forcompiling the first query from the second store.